﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using NHibernate;
using NHibernate.Transform;
using Nissen_Model.Dto;

namespace Nissen_Dao
{
	public class ReportDao : DaoBase
	{
		string connectionString = ConfigurationManager.AppSettings["DbHelperConnectionStringReport"];

        /// <summary>
        /// GP件数统计
        /// </summary>
        public DataTable GetGPCountByDate(DateTime dtfrom,DateTime dtto)
        {
            DataTable dt = new DataTable();

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["DbHelperConnectionStringInstance"]))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.Append("select * from [NMPDB].[nmpdb].[V_Worker] t1 left join");
                sb.Append("(SELECT count(*) cnt,worker_no,nissen_sso FROM [NMPDB].[nmpdb].[T_RecordData]");
                sb.Append(" where [EndWorkDate] > @dtfrom");
                sb.Append(" and [EndWorkDate] < @dtto");
                sb.Append(" group by worker_no,nissen_sso) t");
                sb.Append(" on t1.[Worker_SSO] = t.worker_no and t1.[Nissen_SSO_Code] = t.nissen_sso");
                sb.Append(" order by cnt,t1.nissen_sso_code");

                try
                {
                    SqlCommand sqlCommand1 = new SqlCommand();
                    sqlCommand1.Connection = connection;
                    sqlCommand1.CommandText = sb.ToString();
                    sqlCommand1.Parameters.AddWithValue("@dtfrom", dtfrom.ToShortDateString());
                    sqlCommand1.Parameters.AddWithValue("@dtto", dtto.AddDays(1).ToShortDateString());

                    SqlDataAdapter sdap = new SqlDataAdapter();
                    sdap.SelectCommand = sqlCommand1;
                    sqlCommand1.CommandTimeout = 120;
                    sdap.Fill(dt);

                }
                catch (Exception)
                {
                    throw;
                }

            }
            return dt;
        }

		public DataTable GetGeresultDay()
		{

			DataTable dt = new DataTable();
			
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.CommandText = "select * from V_GERESULT_DAY";

					SqlDataAdapter sdap = new SqlDataAdapter();
					sdap.SelectCommand = sqlCommand1;
					sqlCommand1.CommandTimeout = 120;
					sdap.Fill(dt);

				}
				catch (Exception)
				{
					throw;
				}

			}
			return dt;
		}

		public DataTable GetGeresultDayHour()
		{
			DataTable dt = new DataTable();

			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.CommandText = "select * from V_GERESULT_DAY_HOUR";

					SqlDataAdapter sdap = new SqlDataAdapter();
					sdap.SelectCommand = sqlCommand1;
					sqlCommand1.CommandTimeout = 120;
					sdap.Fill(dt);

				}
				catch (Exception)
				{
					throw;
				}

			}
			return dt;
		}

		public DataTable GetGeresultPerson()
		{

			DataTable dt = new DataTable();

			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.CommandText =@" SELECT 
													main.*,
												   twnm.WorkerSSO,
												   twnm.WorkerName
											FROM   V_GERESULT_PERSON main
												   LEFT JOIN T_Worker_Nissen_Map twnm
														ON  main.E_ID = twnm.NissenSSO
														AND (CONVERT(NVARCHAR(10), MAIN.DATE, 102) = CONVERT(NVARCHAR(10), twnm.CreatDate, 102) 
														OR CONVERT(NVARCHAR(10), MAIN.DATE, 102) = CONVERT(NVARCHAR(10), dateadd(day,1,twnm.CreatDate), 102)
														OR CONVERT(NVARCHAR(10), MAIN.DATE, 102) = CONVERT(NVARCHAR(10), dateadd(day,2,twnm.CreatDate), 102)
														OR CONVERT(NVARCHAR(10), MAIN.DATE, 102) = CONVERT(NVARCHAR(10), dateadd(day,3,twnm.CreatDate), 102)
														)";

					SqlDataAdapter sdap = new SqlDataAdapter();
					sdap.SelectCommand = sqlCommand1;
					sqlCommand1.CommandTimeout = 120;
					sdap.Fill(dt);

				}
				catch (Exception)
				{
					throw;
				}

			}
			return dt;
		}
		/// <summary>
		/// 用户所有错误, 其中语文错误单独统计
		/// </summary>
		public DataTable GetGeresultPersonMissTotalYuwe()
		{
			DataTable dt = new DataTable();

			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.CommandText = "select * from V_GERESULT_PERSON_MISS_TOTAL_YUWEN";

					SqlDataAdapter sdap = new SqlDataAdapter();
					sdap.SelectCommand = sqlCommand1;
					sqlCommand1.CommandTimeout = 120;
					sdap.Fill(dt);

				}
				catch (Exception)
				{
					throw;
				}

			}
			return dt;
		}
		/// <summary>
		/// 用户所有工作量, 其中语文单独统计
		/// </summary>
		public DataTable GetGeresultPersonTotalYuwen()
		{
			DataTable dt = new DataTable();

			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.CommandText = "select * from V_GERESULT_PERSON_TOTAL_YUWEN ";

					SqlDataAdapter sdap = new SqlDataAdapter();
					sdap.SelectCommand = sqlCommand1;
					sqlCommand1.CommandTimeout = 120;
					sdap.Fill(dt);

				}
				catch (Exception)
				{
					throw;
				}

			}
			return dt;
		}

        /// <summary>
        /// 日本側処理
        /// </summary>
        public DataTable GetGeresultWithNihonSide()
        {
            DataTable dt = new DataTable();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    SqlCommand sqlCommand1 = new SqlCommand();
                    sqlCommand1.Connection = connection;
                    sqlCommand1.CommandText = "select * from V_GERESULT_PERSON_TOTAL_YUWEN Where yuwencount > 0";

                    SqlDataAdapter sdap = new SqlDataAdapter();
                    sdap.SelectCommand = sqlCommand1;
                    sqlCommand1.CommandTimeout = 120;
                    sdap.Fill(dt);

                }
                catch (Exception)
                {
                    throw;
                }

            }
            return dt;
        }

        /// <summary>
        /// 日本側処理明細
        /// </summary>
        public DataTable GetGeresultWithNihonSideDetails()
        {
            DataTable dt = new DataTable();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT  t1.* FROM [NMPDB_OLD].[dbo].[GERESULT] t1");
                sb.Append(" join [NMPDB_OLD].[dbo].[TEST_CODE] t2");
                sb.Append(" on t1.[TEST_CODE] = t2.[TEST_CODE]");
                sb.Append(" and t1.[COURSE_LEVEL_ID] = t2.[COURSE_LEVEL_ID]");
                sb.Append(" and t1.[KYOKA_CD] = t2.[KYOKA_CD]");
                sb.Append(" and t1.[UNIT_NO] = t2.[UNIT_NO]");
                sb.Append(" order by t1.ety_id_1,t1.ety_id_2");
                try
                {
                    SqlCommand sqlCommand1 = new SqlCommand();
                    sqlCommand1.Connection = connection;
                    sqlCommand1.CommandText = sb.ToString();

                    SqlDataAdapter sdap = new SqlDataAdapter();
                    sdap.SelectCommand = sqlCommand1;
                    sqlCommand1.CommandTimeout = 120;
                    sdap.Fill(dt);

                }
                catch (Exception)
                {
                    throw;
                }

            }
            return dt;
        }

		/// <summary>
		/// 用户校验工作工作量
		/// </summary>
		public DataTable GetGeresultPersonSysUpdId()
		{
			DataTable dt = new DataTable();

			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.CommandText = "select * from V_GERESULT_PERSON_SYS_UPD_ID";

					SqlDataAdapter sdap = new SqlDataAdapter();
					sdap.SelectCommand = sqlCommand1;
					sqlCommand1.CommandTimeout = 120;
					sdap.Fill(dt);

				}
				catch (Exception)
				{
					throw;
				}

			}
			return dt;
		}
	}
}
